#  python3 sqllite3
import sqlite3 as db
import os

class MySequence:
    """docstring for mySequence"""
    def __init__(self, path):
        self.path = path
        self.conn = self.getConnect()
        self.cu = self.getCursor()
        
    def getConnect(self):
        path = self.path
        '''
    返回数据库连接，如果数据库文件不存在返回内存数据库连接
        '''
        if os.path.exists(path) and os.path.isfile(path):
            conn = db.connect(path)
        else:
            path1 = os.path.split(path)[0]
            if os.path.exists(path1):
                conn = db.connect(path)
            else:
                os.makedirs(path1)
                conn = db.connect(path)
        return conn

    def getCursor(self):
        '''
    返回游标 如果conn不存在返回 内容数据库游标
        '''
        conn = self.conn
        if conn is not None:
            cu= conn.cursor()
        else:
            print("数据库连接不存在，建立内存数据库")
            cu = getConnect('').cursor()
        return cu
        
    def createMySequenceTable(self):
        cu= self.cu
        conn = self.conn
        createMySequenceTableSql = '''
        create table `mySequence`(
            id int not null UNIQUE,    
            name varchar2(50) PRIMARY KEY  not null,
            for_table varchar2(50) not null,
            currentValue int not null,
            step int not null
            )
        '''
        insertFristSequence= 'insert into mySequence values(:id, :name, :for_table, :value, :step)'
        try:
            cu.execute(createMySequenceTableSql)
            cu.execute(insertFristSequence,{'id':1, 'name':'mySequence_id_seq', 'for_table':'mySequence', 'value':1, 'step':1, })
            conn.commit()
        except db.OperationalError as ex:
            #raise ex
            print("出错啦 %s" %ex)
            return 0
        return 1

    def  mySequenceNextValue(self,seqName):
        cu = self.cu
        getValueSql = 'select step, currentValue from mySequence where name = :name'
        updateValueSql = 'update mySequence set currentValue = :value where name = :name'
        cu.execute(getValueSql, {'name':seqName})
        (step, nextValue) = cu.fetchall()[0]
        nextValue += step
        data = {'name':seqName, 'value':nextValue}
        cu.execute(updateValueSql, data)
        self.conn.commit()
        return nextValue

    def mySequenceCurrentValue(self, name):
        getValueSql = 'select currentValue from mySequence where name = ?'
        return cu.execute(getValueSql, name)

    def createSequence(self,seq_name,table_name):
        cu = self.cu
        createSequenceSql='insert into mySequence values(:id, :name, :for_table, :value, :step)'
        data = {'id':self.mySequenceNextValue('mySequence_id_seq'), 'name':seq_name, 'for_table':table_name, 'value':0, 'step':1 }
        try:
            cu.execute(createSequenceSql, data)
            return 1
        except Exception as ex:
            raise ex
            print("出错啦 %s" % ex) 
            return 0
